SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[paObtenerVista]

@Nombre VARCHAR(100),
@NombreTablaSubyacente VARCHAR(100) = NULL

AS
SET NOCOUNT ON
SELECT OBJ.Id [ID], OBJ.Name Nombre, USR.Name Propietario, XType TipoDeObjeto,
CASE PS.ProtectType WHEN 205 THEN 1 ELSE 0 END PermisoSelect, 
CASE PIN.ProtectType WHEN 205 THEN 1 ELSE 0 END PermisoInsert, 
CASE PU.ProtectType WHEN 205 THEN 1 ELSE 0 END PermisoUpdate, 
CASE PD.ProtectType WHEN 205 THEN 1 ELSE 0 END PermisoDelete 
FROM SYSOBJECTS OBJ
INNER JOIN SYSUSERS USR ON OBJ.Uid = USR.Uid 
LEFT OUTER JOIN SYSPROTECTS PS ON PS.Id = OBJ.Id AND (IS_MEMBER(USER_NAME(PS.Uid)) = 1 OR PS.Grantor = USER_ID()) AND PS.Action = 193 AND PS.ProtectType = 205
LEFT OUTER JOIN SYSPROTECTS PIN ON PIN.Id = OBJ.Id AND (IS_MEMBER(USER_NAME(PIN.Uid)) = 1 OR PIN.Grantor = USER_ID()) AND PIN.Action = 195 AND PIN.ProtectType = 205
LEFT OUTER JOIN SYSPROTECTS PU ON PU.Id = OBJ.Id AND (IS_MEMBER(USER_NAME(PU.Uid)) = 1 OR PU.Grantor = USER_ID()) AND PU.Action = 197 AND PU.ProtectType = 205
LEFT OUTER JOIN SYSPROTECTS PD ON PD.Id = OBJ.Id AND (IS_MEMBER(USER_NAME(PD.Uid)) = 1 OR PD.Grantor = USER_ID()) AND PD.Action = 196 AND PD.ProtectType = 205
WHERE Xtype = 'V' AND OBJ.Name = @Nombre

UNION

SELECT OBJ.Id [ID], OBJ.Name Nombre, USR.Name Propietario, XType TipoDeObjeto,
0, 0, 0, 0  
FROM SYSOBJECTS OBJ
INNER JOIN SYSUSERS USR ON OBJ.Uid = USR.Uid 
WHERE Xtype = 'U' AND OBJ.Name = @NombreTablaSubyacente
ORDER BY TipoDeObjeto
GO
GRANT EXECUTE ON  [dbo].[paObtenerVista] TO [CentroDeDesignaciones]
GRANT EXECUTE ON  [dbo].[paObtenerVista] TO [public]
GRANT EXECUTE ON  [dbo].[paObtenerVista] TO [SoloVer]
GO
